<?php

/**
 * Datagrid
 * Little script for editing DB data
 * @version $Id$
 * @copyright 2010
 */
/**
 * Datagrid
 *
 * @package
 * @author kubicek
 * @copyright Copyright (c) 2010
 * @version $Id$
 * @access public
 */
class Datagrid
{
	private $table = '';
	private $order_by = '';
	private $columns = Array();
	private $filtered_columns = Array();
	private $is_autoincrement = false;
	private $autoinc_column = 0;
	private $primary = 0;
	private $show_only = false;
	private $query = '';
	private $fieldset = true;

	/**
	 * Constructor
	 */
	function __construct($table)
	{
		$this->table = $table;
		$this->update_table();
	}

	/**
	 * Datagrid::set_table()
	 * Set table for datagrid
	 * @param mixed $table
	 * @return
	 */
	public function set_table($table)
	{
		$this->table = $table;
	}

	/**
	 * Datagrid::set_order()
	 * Set ORDER BY clausule in query
	 * @param mixed $column
	 * @param mixed $type
	 * @return
	 */
	public function set_order($column,$type)
	{
		$this->order_by = ' ORDER BY '.$column.' '.$type;
	}

	/**
	 * Datagrid::add_filter()
	 * Add filtered column
	 * @param mixed $column
	 * @return
	 */
	public function add_filter($column)
	{
		$this->filtered_columns[] = $column;
	}

	/**
	 * Datagrid::show_only()
	 * Set if you want to only show data, not edit
	 * @param mixed $state TRUE/FALSE
	 * @return
	 */
	public function show_only($state = true)
	{
		$this->show_only = $state;
	}

	/**
	 * Datagrid::set_query()
	 * Set your own custom query
	 * @param mixed $query
	 * @return
	 */
	public function set_query($query)
	{
		$this->query = $query;
	}

	/**
	 * Datagrid::fieldset()
	 * Do you want fieldset?
	 * @param mixed $state TRUE/FALSE
	 * @return
	 */
	public function fieldset($state)
	{
		$this->fieldset = $state;
	}

	/**
	 * Datagrid::add_edit_option()
	 * Adds editation link
	 * @param mixed $link
	 * @param mixed $title
	 * @return
	 */
	public function add_edit_option($link,$title)
	{
		$this->edit_option = true;
		$this->edit_option_link = $link;
		$this->edit_option_title = $title;
	}

	/**
	 * Datagrid::add_delete_option()
	 * Adds delete link
	 * @param mixed $link
	 * @param mixed $title
	 * @return
	 */
	public function add_delete_option($link,$title)
	{
		$this->delete_option = true;
		$this->delete_option_link = $link;
		$this->delete_option_title = $title;
	}

	/**
	 * Datagrid::update_table()
	 * Detect if there is some data in $_POST and if they belong to datagrid and update them to table
	 * @return
	 */
	public function update_table()
	{
		global $core;

		if($core->post('table'))
		{
			$core->msg('Updating values for table '.$core->post('table').'...');

			$original_table = $this->table; //backup old table
			$this->set_table($core->post('table')); //set the new one

			$this->get_table_info(); //get info

			/*mysql_query("TRUNCATE TABLE ".$this->table); //empty table
			if($this->is_autoincrement) //we must set autoinc value to 1, or after few updates there will be HUGE id values
			{
				mysql_query("ALTER TABLE ".$this->table." AUTO_INCREMENT = 1");
			}*/

			$values = Array();

			for($i = 0; $i < sizeof($this->columns); $i++) //get values
			{
				$values[] = $core->post($this->columns[$i]);
			}

			$values = $this->normalize_array($values);

			for($i = 0; $i < sizeof($values); $i++)
			{
				$null_item = true; //switch for null record

				if($this->is_row_in_table($values[$i]))
				{
					$query = 'UPDATE '.$this->table.' SET ';
					for($j = 0; $j < sizeof($this->columns); $j++)
					{
						if($j != $this->primary)
						{
							$query .= $this->columns[$j].' = \''.$values[$i][$j].'\', ';
							if(strlen($values[$i][$j]) > 0) //no value?
							{
								$null_item = false;
							}
						}
					}
					$query = substr($query, 0, -2);

					$query .= ' WHERE '.$this->columns[$this->primary];

					if($this->primary_type == 'number')
					{
						$query .= ' = '.$values[$i][$this->primary];
					}
					else
					{
						$query .= ' LIKE \''.$values[$i][$this->primary].'\'';
					}

					//echo $query,'<br />';
					if(!$null_item)
					{
						mysql_query($query);
					}
					else
					{
						if($this->primary_type == 'number')
						{
							mysql_query("DELETE FROM ".$this->table.' WHERE '.$this->columns[$this->primary].' = '.$values[$i][$this->primary]);
						}
						else
						{
							echo "DELETE FROM ".$this->table.' WHERE '.$this->columns[$this->primary].' LIKE \''.$values[$i][$this->primary].'\'';
							mysql_query("DELETE FROM ".$this->table.' WHERE '.$this->columns[$this->primary].' LIKE \''.$values[$i][$this->primary].'\'');
						}
					}
				}
				else
				{
					$query = "INSERT INTO ".$this->table." VALUES(";
					for($j = 0; $j < sizeof($values[$i]); $j++)
					{

						if(strlen($values[$i][$j]) > 0) //no value?
						{
							$null_item = false;
						}

						if($j == $this->autoinc_column && $this->is_autoincrement == true) //is it autoinc value? Set it to zero
						{
							$query .= '0,';
						}
						else //otherwise...
						{
							if($j == sizeof($values[$i])-1) //is it the last one? Dont add ,
							{
								$query .= "'".addslashes($values[$i][$j])."'";
							}
							else
							{
								$query .= "'".addslashes($values[$i][$j])."',";
							}
						}
					}
					$query .= ')';

					//echo $query,'<br />';

					if($null_item == false) //is there any data in query?
					{
						mysql_query($query);
					}
				}
			}

			$this->set_table($original_table); //set back original table

			$core->refresh_js('');
		}
	}

	private function normalize_array($array)
	{
		global $core;
		$new = Array(Array());

		for($i = 0; $i < sizeof($array); $i++)
		{
			for($j = 0; $j < sizeof($array[$i]); $j++)
			{
				$new[$j][$i] = $array[$i][$j];
			}
		}

		return $new;
	}

	private function is_row_in_table($row)
	{
		global $core;

		if(strlen($row[$this->primary]) == 0 || ($row[$this->primary] == 0 && is_numeric($row[$this->primary])))
		{
			return false;
		}

		$query = mysql_query("SELECT * FROM ".$this->table." WHERE ".$this->columns[$this->primary].' = '.$row[$this->primary]);
		if(@mysql_num_rows($query) > 0)
		{
			$this->primary_type = 'number';
			return true;
		}
		else
		{
			$query = mysql_query("SELECT * FROM ".$this->table." WHERE ".$this->columns[$this->primary].' LIKE \''.$row[$this->primary].'\'');
			if(mysql_num_rows($query) > 0)
			{
				$this->primary_type = 'string';
				return true;
			}
		}

		return false;
	}

	/**
	 * Datagrid::get_table_info()
	 * Fill private property columns with info about table
	 * @return
	 */
	private function get_table_info()
	{
		global $core;

		$query = mysql_query("DESC ".$this->table); //we want description of table
		if(mysql_num_rows($query) > 0)
		{
			$this->columns = Array(); //lets save columns names
			$i = 0;
			while($row = mysql_fetch_array($query))
			{
				//echo 'Row: '.$row['Field'].' is '.$row['Type'].'<br />';
				$this->columns[] = $row['Field'];

				if($row['Extra'] == 'auto_increment') //if this table has autoinc value, we must set it to 0 when updating
				{
					$this->is_autoincrement = true;
					$this->autoinc_column = $i;
				}

				//get primary key of table
				if($row['Key'] == 'PRI')
				{
					$this->primary = $i;
				}

				$i++;
			}
		}
		else //can`t DESC table
		{
			$core->err('Table '.$this->table.' is corrupted! Datagrid can`t display it, no columns.');
		}
	}

	/**
	 * Datagrid::draw_grid()
	 * Draw grid for editing/adding/deleting rows in table
	 * @return
	 */
	public function draw_grid()
	{
		global $core;

		$this->get_table_info();

		if($this->query) $query = mysql_query($this->query);
		else $query = mysql_query("SELECT * FROM ".$this->table.$this->order_by); //what is inside?

		//lets do some nice output
		if($this->show_only == true)
		{
			if($this->fieldset == true) echo '<fieldset><legend>Content of table ',$this->table,'</legend>';
			echo '<table class="nice" style="'.$this->style.'">
					<tr>';
		}
		else
		{
			if($this->fieldset == true) echo '<fieldset><legend>Content of table ',$this->table,'</legend>';
			echo '<form name="'.$this->table.'" action="" method="post">
					<table class="nice" style="'.$this->style.'">
					<tr>';
		}

		//table headers with columns names
		for($i = 0; $i < sizeof($this->columns); $i++)
		{
			if(!in_array($this->columns[$i],$this->filtered_columns))
			{
				if($i == $this->primary)
				{
					echo '<th>',$this->columns[$i],'[PK]</th>';
				}
				else
				{
					echo '<th>',$this->columns[$i],'</th>';
				}
			}
		}

		if($this->edit_option == true || $this->delete_option == true)
		{
			echo '<th>Options</th>';
		}

		echo '</tr>';

		$empty_new_row = true;
		if($empty_new_row && $this->show_only != true) //lets create one row empty, for new records
		{
			echo '<tr>';
			for($i = 0; $i < sizeof($this->columns); $i++)
			{
				if(!in_array($this->columns[$i],$this->filtered_columns)) //is it filtered?
				{
					echo '<td><input type="text" name="',$this->columns[$i],'[]" value="" /></td>
	';
				}
				else
				{
					echo '<input type="hidden" name="',$this->columns[$i],'[]" value="" />
	';
				}
			}

			echo '</tr>';
			$empty_new_row = false;
		}

		if(mysql_num_rows($query) > 0)
		{
			//lets create inputs with values
			while($row = mysql_fetch_array($query))
			{
				echo '<tr>';
				for($i = 0; $i < sizeof($this->columns); $i++)
				{
					if($this->show_only == true)
					{
						if(!in_array($this->columns[$i],$this->filtered_columns)) //is it filtered?
						{
							echo '<td>',$row[$this->columns[$i]],'</td>
	';
						}
					}
					else
					{
						if(!in_array($this->columns[$i],$this->filtered_columns)) //is it filtered?
						{
							echo '<td><input type="text" name="',$this->columns[$i],'[]" value="',$row[$this->columns[$i]],'" /></td>
	';
						}
						else
						{
							echo '<input type="hidden" name="',$this->columns[$i],'[]" value="',$row[$this->columns[$i]],'" />
	';
						}
					}

					if($i == $this->primary) $id = $row[$this->columns[$i]];
				}

				if($this->edit_option == true || $this->delete_option == true)
				{
					echo '<td>';
					if($this->edit_option == true)
					{
						echo '<a href="'.str_replace('[ID]',$id,$this->edit_option_link).'">'.$this->edit_option_title.'</a>';
					}

					if($this->delete_option == true)
					{
						echo '<a href="'.str_replace('[ID]',$id,$this->delete_option_link).'">'.$this->delete_option_title.'</a>';
					}
					echo '</td>';
				}

				echo '</tr>';
			}
		}

		if($this->show_only == true)
		{
			echo '
</table>';
			if($this->fieldset == true) echo '</fieldset>';
		}
		else
		{
			echo '
</table>
<input type="hidden" name="table" value="',$this->table,'" />
<input type="submit" value="Edit values" name="',$this->table,'"/></form><br />
Hint: Delete all values except [PK] in one row to remove that row from DB. Use first blank row to insert a new row to DB.
';
			if($this->fieldset == true) echo '</fieldset>';
		}
	}
}

?>